<?php

// Защита от прямого доступа к файлу
defined('_JEXEC') or die('Restricted access');

// Подключаем библиотеки Joomla
jimport('joomla.filesystem.file');

// Создаем класс парсера
class DistributorParser {
	
	protected $id;
	protected $alias;
	protected $dir;
	protected $file;
	
	protected $stockId = array();
	
	protected $msg;
	protected $log;
	
	protected $data = array();
	protected $number = array();
	protected $product = array();
	
	public function setId($id) {
		$this->id = $id;
		return true;
	}
	
	public function getId() {
		return $this->id;
	}
	
	public function setAlias($alias) {
		$this->alias = $alias;
		$this->onDir();
		return true;
	}
	
	public function getAlias() {
		return $this->alias;
	}
	
	private function onDir() {
		$this->dir = JPATH_SITE.DS.'docs'.DS.'distributors'.DS.$this->alias;
		return true;
	}
	
	public function setMsg($msg) {
		$this->msg = $msg;
	}
	
	public function addMsg($msg) {
		$this->msg .= $msg."\n";
	}
	
	public function getMsg() {
		return $this->msg;
	}
	
	public function parsing() { // Управляющая функция парсинга
		//if (true == $this->truncateTable('#__pricer_product')) { $this->addMsg('Таблица "#__pricer_product" очищена;'); } else { $this->addMsg('Ошибка очистки таблицы "#__pricer_product";'); }
		if (true != $this->stockId['treolan-stock-russia'] = $this->onStock('treolan-stock-russia', 'Российский склад Treolan', 10)) { return false; }
		if (true != $this->stockId['treolan-transit'] = $this->onStock('treolan-transit', 'Транзит Treolan', 60)) { return false; }
		if (true == $this->findPriceXLS())	{ /* Находим прайс							*/ } else { $this->addMsg("Не найден прайс-лист {$this->alias};"); return false; }
		if (true == $this->loadPriceHtml())	{ /* Загружаем данные в массив	*/ } else { $this->addMsg("Не удается прочитать прайс-лист {$this->alias};"); return false; }
		if (true == $this->identifyCol())		{ /* Идентифицируем колонки			*/ } else { $this->addMsg("Формат прайс-листа {$this->alias} некорректен;"); return false; }
		if (true == $this->loadToSQL())			{ /* Загружаем данные в базу		*/ } else { $this->addMsg("Загрузка иныыормации о товаре в базу не удалась;"); return false; }
	}
	
//	private function truncateTable($table) { // Очищает таблицу
//		// $this->addMsg('priceparsing.treolan.php function truncateTable($table);');
//		$db =& JFactory::getDBO();
//		$query = "TRUNCATE TABLE {$db->nameQuote($table)};";
//		$db->setQuery($query);
//		$db->query();
//		return true;
//	}
	
	private function onStock($alias, $name, $deliveryTime) {
		// $this->addMsg('priceparsing.treolan.php function onStock(); // Определяем id складов, при необходимости добавляем их');
		$db =& JFactory::getDBO();
		$query = "SELECT id FROM #__pricer_stock WHERE '{$alias}' <=> #__pricer_stock.alias;";
		$db->setQuery($query);
		$stockId = $db->loadResult();
		if (true == $stockId) {
			return $stockId;
		} else {
			$this->addMsg("Добавляем склад: {$name}");
			$query = "INSERT INTO #__pricer_stock (`id`, `contractor_id`, `alias`, `name`, `delivery_time`, `comment`, `published`, `updated`, `autor_id`) VALUES (0, {$this->id}, '{$alias}', '{$name}', {$deliveryTime}, '', 1, NOW(), 0);";
			$db->setQuery($query);
			$db->query();
			$this->addMsg($query);
			$query = "SELECT id FROM #__pricer_stock WHERE '{$alias}' <=> #__pricer_stock.alias;";
			$db->setQuery($query);
			$stockId = $db->loadResult();
			if (true == $stockId) {
				$this->addMsg("Добавлен склад: {$name}");
			} else {
				$this->addMsg("Невозможно добавить новый склад: {$name}. Все пропало.");
			}	
		}
	return $stockId;
	}
	
	private function findPriceXLS() { // Находит прайс в папке дистрибьютора
		// $this->addMsg('priceparsing.treolan.php function findPriceXLS();');
		if (false == $files = JFolder::files($this->dir, $filter='.xls', false, false, false)) {
			return false;
		}
		$this->file = $this->dir.DS.$files[0];
		return true;
	}
	
	private function loadPriceHtml() {
		// $this->addMsg('priceparsing.treolan.php function loadPriceHtml(); // Загрузка данных в массив');
		$dom = new DomDocument();
		$dom->loadHtmlFile($this->file);
		$row = 0;
		$xpath = new DOMXPath($dom);
		foreach ($xpath->query('.//tr') as $tr) {
			$col = 0;
			foreach ($tr->childNodes as $td) {
				// echo $td->nodeValue."\n";
				$this->data[$row][$col] = $td->nodeValue;
				$col++;
			}
			$row++;
		}
		return true;
	}
	
	private function identifyCol() {
		// $this->addMsg('priceparsing.treolan.php function identifyCol(); // Идентифицируем колонки');
		for ($col=0; $col<sizeof($this->data[0]); $col++) {
			switch ($this->data[0][$col]) {
				case 'Производитель'	:	$this->number['vendor']					= $col;	break;
				case 'Артикул'				:	$this->number['article']				= $col;	break;	
				case 'Наименование'		:	$this->number['name']						= $col;	break;
				case 'Цена*'					:	$this->number['price']					= $col;	break;
				case 'Цена руб.**'		:	$this->number['price_RUR']			= $col;	break;
				case 'Св.'						:	$this->number['stock_russia']		= $col;	break;
				case 'Св.+Тр.'				:	$this->number['stock_transit']	= $col;	break;
				case 'Доп.'						:	$this->number['comment']				= $col;	break;
				case 'Гар.'						:	$this->number['warranty']				= $col;	break;
				case 'Вес [кг.]'			:	$this->number['mass']						= $col;	break;
				case 'Объем [литры]'	:	$this->number['volume']					= $col;	break;
			}
		}
		if (0 ===	$this->number['article'] and $this->number['vendor'] and $this->number['name'] and $this->number['price'] and $this->number['price_RUR'] and $this->number['stock_russia'] and $this->number['stock_transit'] and $this->number['comment'] and $this->number['warranty']) {
			return true;
		} else {
			return false;
		}
	}

	private function loadToSQL() {
		// $this->addMsg('priceparsing.treolan.php function loadToSQL();');
		$db =& JFactory::getDBO();
		// Цикл по всем строкам
		for ($row=1; $row<sizeof($this->data); $row++) {
			if (1 == sizeof($this->data[$row])) { // Строка с категорией
				$this->onCategoryId($row);
				continue;
			} elseif (true == $this->data[$row][$this->number['article']] and $this->data[$row][$this->number['vendor']]) { // Строка с товаром
				$this->onVendorId($row);
				$this->onArticle($row);
				$this->onName($row);
				$this->onWarranty($row);
				$this->onMass($row);
				$this->onVolume($row);
				$this->onProductId();
				if (true == $this->product['id']) {
					$this->onPrice($row);
					$this->onQuantity($row);
				}
			}
		}
		
		$this->msg .= "Обработчик Treolan в разработке.\n";
		return true;
	}
	
	private function onCategoryId($row) {
		// $this->addMsg('priceparsing.treolan.php function onCategoryId(); // Определяем id категории');
		$db =& JFactory::getDBO();
		$query = "SELECT id, category_id, prefix, title FROM #__pricer_category_synonim WHERE {$db->Quote($this->data[$row][0])} <=> #__pricer_category_synonim.title;";
		$db->setQuery($query);
		$category = $db->loadAssocList();
		if (true == $category[0]['category_id']) { // Есть данное написание и присвоена категория к нему
			$this->product['categoryId'] = $category[0]['category_id'];
			$this->product['prefix'] = $category[0]['prefix'];
			return $this->product['categoryId'];
		} elseif (false == $category[0]['id']) { // Нет данного написания в базе - добавляем его
			$this->addMsg("Добавляем категорию {$category[0]['name']}");
			$query = "INSERT INTO #__pricer_category_synonim (id, category_id, prefix, title, contractor_id, created_user_id, created_time, modified_user_id, modified_time) VALUES (0, NULL, NULL, {$db->Quote($this->data[$row][0])}, {$db->Quote($this->id)}, 0, NOW(), NULL, NULL);";
			$db->setQuery($query);
			$db->query();
			$this->product['categoryId'] = 0;
			$this->product['prefix'] = '';
			return 0;
		} elseif ((false == $category[0]['category_id']) and (true == $category[0]['id'])) { // Есть описание, но нет привязки
			$this->product['categoryId'] = 0;
			$this->product['prefix'] = '';
			return 0;
		}
	}
	
	private function onVendorId($row) {
		$db =& JFactory::getDBO();
		$query = "SELECT id, vendor_id, name FROM #__pricer_vendor_synonim WHERE {$db->Quote($this->data[$row][$this->number['vendor']])} <=> #__pricer_vendor_synonim.name;";
		$db->setQuery($query);
		$vendor = $db->loadAssocList();
		if (true == $vendor[0]['vendor_id']) { // Есть описание и ему присвоен вендор
			$this->product['vendorId'] = $vendor[0]['vendor_id'];
			return $this->product['vendorId'];
		} elseif (false == $vendor[0]['id']) { // Нет описания в базе - добавляем его
			$this->addMsg("Добавляем производителя {$vendor[0]['name']}");
			$query = "INSERT INTO #__pricer_vendor_synonim (id, vendor_id, name) VALUES (0, NULL, {$db->Quote($this->data[$row][$this->number['vendor']])});";
			$db->setQuery($query);
			$db->query();
			$this->product['vendorId'] = 0;
			return 0;
		} elseif ((false == $vendor[0]['vendor_id']) and (true == $vendor[0]['id'])) { // Есть описание, но нет привязки
			$this->product['vendorId'] = 0;
			return 0;
		}
	}

	private function onArticle($row) {
		$db =& JFactory::getDBO();
		if (true == $this->data[$row][$this->number['article']]) {
			$this->product['article'] = $db->Quote($this->data[$row][$this->number['article']]);
			return true;
		} else {
			$this->product['article'] = '';
			return false;
		}
	}

	private function onName($row) {
		$db =& JFactory::getDBO();
		if (true == $this->product['prefix']) {
			$this->product['name'] = $db->Quote("{$this->product['prefix']} {$this->data[$row][$this->number['name']]}");
		} else {
			$this->product['name'] = $db->Quote($this->data[$row][$this->number['name']]);
		}
		return true;
	}

	private function onWarranty($row) {
		$db =& JFactory::getDBO();
		if (true == $this->data[$row][$this->number['warranty']]) {
			$this->product['warranty'] = $db->Quote($this->data[$row][$this->number['warranty']]);
		} else {
			$this->product['warranty'] = 'NULL';
		}
		return true;
	}

	private function onMass($row) {
		$db =& JFactory::getDBO();
		if (true == $this->data[$row][$this->number['mass']]) {
			$this->product['mass'] = $db->Quote($this->data[$row][$this->number['mass']]);
		} else {
			$this->product['mass'] = 'NULL';
		}
		return true;
	}

	private function onVolume($row) {
		$db =& JFactory::getDBO();
		if (true == $this->data[$row][$this->number['volume']]) {
			$this->product['volume'] = $db->Quote($this->data[$row][$this->number['volume']]);
		} else {
			$this->product['volume'] = 'NULL';
		}
		return true;
	}

	private function onProductId() {
		$db =& JFactory::getDBO();
		$id = $this->checkProduct($this->product['vendorId'], $this->product['article']); 
		if (true == $id) { // Товар присутствует в базе
			$this->product['id'] = $id;
			// $this->addMsg("Товар присутствует в базе: \$id = {$id}");
			return $id;
		} elseif (true == $this->product['vendorId'] and $this->product['categoryId'] and $this->product['article'] and $this->product['name']) { // Товар отсутствует в базе - его можно добавить
			$query =	"INSERT INTO #__pricer_product (id, vendor_id, category_id, article, name, mass, volume, warranty, published, actually, actually_to, updated, autor_id) VALUES (0, {$this->product['vendorId']}, {$this->product['categoryId']}, {$this->product['article']}, {$this->product['name']}, {$this->product['mass']}, {$this->product['volume']}, {$this->product['warranty']}, 1, 1, '2100-01-01', NOW(), NULL);";
			$db->setQuery($query);
			$db->query();
			$id = $this->checkProduct($this->product['vendorId'], $this->product['article']);
			if (true == $id) {
				$this->product['id'] = $id;
				$this->addMsg("Товар добавлен в базу: \$id = {$id};");
				return $id;
			} else {
				$this->product['id'] = 0;
				$this->addMsg("Произошла ошибка при добавлении товара: \$article = {$this->product['article']};");
				return 0;
			}
		} else { // Недостаточно данных о товаре для его добавления
			$this->product['id'] = 0;
			// $this->addMsg("Недостаточно данных о товаре для его добавления: \$article = {$this->product['article']};");
			return 0;
		}
	}
	
	private function checkProduct($vendorId, $article) { // Проверяет существование товара
		$db =& JFactory::getDBO();
		$query = "SELECT #__pricer_product.id FROM #__pricer_product WHERE {$vendorId} = #__pricer_product.vendor_id AND {$article} = #__pricer_product.article;";
		$db->setQuery($query);
		$id = $db->loadResult();
		if (true == $id) {
			return $id;
		} else {
			return 0;
		}
	}
	
	private function onPrice($row) {
		$db =& JFactory::getDBO();
		// Определяем цену и валюту
		if (true == $this->data[$row][$this->number['price']]) {
			$this->product['currency'] = "'USD'";
			$this->product['price'] = $this->clearPrice($this->data[$row][$this->number['price']]);
		} elseif (true == $this->data[$row][$this->number['price_RUR']]) {
			$this->product['currency'] = "'RUR'";
			$this->product['price'] = $this->clearPrice($this->data[$row][$this->number['price_RUR']]);
		} else {
			$this->product['currency'] = "'USD'";
			$this->product['price'] = $this->clearPrice('0');
		}
		$this->addMsg("Заносим цену в базу: {$this->product['price']} {$this->product['currency']}");
		$query="INSERT INTO `#__pricer_price`(`contractor_id`, `product_id`, `updated`, `price`, `currency_id`, `price_type_id`, `actually_to`) VALUES ({$this->id}, {$this->product['id']}, now(), {$this->product['price']}, {$this->product['currency']}, 'RDP', ADDDATE(now(),3));";
		$db->setQuery($query);
		$db->query();
		$this->addMsg($query);
		return true;
	}
	
	private function onQuantity($row) {
		$db =& JFactory::getDBO();
		// Определяем количество товара на складе
		if (true == $this->data[$row][$this->number['stock_russia']]) {
			$this->product['stock_russia'] = $this->clearQuantity($this->data[$row][$this->number['stock_russia']]);
		} else {
			$this->product['stock_russia'] = $this->clearQuantity('0');
		}
		$this->addMsg("Количество на складе: {$this->product['stock_russia']}");
		// Заносим значение в базу
		$query="INSERT INTO `#__pricer_product_quantity` (`product_id`, `stock_id`, `updated`, `actually_to`, `quantity`) VALUES ({$this->product['id']}, {$this->stockId['treolan-stock-russia']}, now(), ADDDATE(now(), 3), {$this->product['stock_russia']});";
		$db->setQuery($query);
		$db->query();
		//$this->addMsg($query);
		// Определяем количество товара в транзите
		if (true == $this->data[$row][$this->number['stock_russia']]) {
			$this->product['stock_transit'] = $this->clearQuantity($this->data[$row][$this->number['stock_transit']]) - $this->product['stock_russia'];
		} else {
			$this->product['stock_transit'] = $this->clearQuantity('0');
		}
		$this->addMsg("Количество в транзите: {$this->product['stock_transit']}");
		// Заносим значение в базу
		$query="INSERT INTO `#__pricer_product_quantity` (`product_id`, `stock_id`, `updated`, `actually_to`, `quantity`) VALUES ({$this->product['id']}, {$this->stockId['treolan-transit']}, now(), ADDDATE(now(), 3), {$this->product['stock_transit']});";
		$db->setQuery($query);
		$db->query();
		//$this->addMsg($query);
		return true;
	}
	
	private function clearString($string) {
		$del[] = '[\]';		$ins[] = '&#92;';
		$del[] = "[']";		$ins[] = '&#39;';
		$del[] = '["]';		$ins[] = '&#34;';
		for ($i=0; $i<sizeof($del); $i++) {
			$string = ereg_replace($del[$i], $ins[$i], $string);
		}
		return $string;
	}
	
	private function clearPrice($string) {
		$string = ereg_replace('[,]', '.', $string);
		$string = ereg_replace('[ ]', '', $string);
		return doubleval($string);
	}
	
	private function clearQuantity($string) {
		$int = ereg_replace('[^0-9]*', '', $string); // убираем из строки все, что не цифра
		if (true == $int) {
			return $int;
		} else {
			$string = utf8_strtolower($string);
			switch ($string) {
				case '0': return 0; // В противном случае нули выбывают из обработки
				case 'склад' : return 1;

				case 'резерв' : return 0;
				case 'транзит' : return 1;
				case 'много' : return 10;
				case 'мало' : return 5;
				default : $this->msg .= 'Необходим новый кейс обработки количества: ['.$string."].\n"; return 0;
			}
		}
	}
}
